	/* 
 * 2004 JAN 24 SHP
 *		1. Added copyright notice
 *		2. Added code comments
 *		3. Changed Xobject inheritence issue. Also all objects must have 
 *			a primary key attribute.
 *		4. Added support for non identity primary key.
 */

using System;
using System.Text;
using System.Reflection;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using FrameworkQ.ObjectSpace.Data.SqlObjects;
using FrameworkQ.ObjectSpace.Data.SqlObjects.Generation;
using FrameworkQ.ObjectSpace;
using FrameworkQ.ObjectSpace.Data.Schema;
using FrameworkQ.ObjectSpace.Data.Providers;

namespace FrameworkQ.ObjectSpace
{
	/// <summary>
	/// Builds various action command object for various cases 
	/// like insert, delete, update etc.
	/// </summary>
	public class ActionCommandBuilder
	{
		private DatabaseConnection _dbConn = null;
        private Configuration _configuration = null;
		public ActionCommandBuilder(DatabaseConnection dbConn, Configuration configuration)
		{
            _configuration = configuration;
			_dbConn = dbConn;
		}

		/// <summary>
		/// Creates an insert command
		/// </summary>
		/// <param name="schema">Schema of the object</param>
		/// <param name="connCache">The connection cache</param>
		/// <returns>Action command object that has been created</returns>
		public  ActionCommand CreateInsertCommand(ObjectSchema schema )
		{
            ISqlDataTypeTranslator translator = _configuration.Provider.GetDataTypeTranslator();
			// Build an insert query
			InsertQuery query = new InsertQuery(schema.TableName);
			ArrayList parmList = new ArrayList();
			foreach (object key in schema.Mapping.Keys)
			{
				PropertyMap pmap = (PropertyMap) schema.Mapping[key];
				string propertyName =(string) pmap.ColumnName;
				
				// is not identity.
				if (!pmap.IsIdentity)
				{	
					query.Items.Add( new UpdateItem(propertyName, SqlExpression.Parameter("@"+propertyName)));
					
					DbParameter parm = CreateParameter();
                    parm.ParameterName = string.Format("{0}{1}", translator.ParameterIdentifier, propertyName);
					parm.DbType = ObjectSchema.GetDBTypeFromSchemaRow(pmap.SchemaRow);
					parm.Size = (int) pmap.SchemaRow ["ColumnSize"];
					parmList.Add(parm);
				}				
			}

            ISqlGenerator generator = _configuration.Provider.GetGenerator();
			string sql = generator.GenerateInsertStatement (query);

            // TODO : Make @@IDENTITY AND @@ROWCOUNT DB Compatible
			if (schema.Mapping.Primary.IsIdentity)
			{
                sql = string.Format("{0}{1} SELECT @@IDENTITY", sql, _dbConn.Provider.GetDataTypeTranslator().NextStatementIdentifier); ;
			}
			else
			{
                sql = string.Format("{0}{1} SELECT @@ROWCOUNT", sql, _dbConn.Provider.GetDataTypeTranslator().NextStatementIdentifier); ;
			}

			IDbConnection connection = _dbConn.Open();
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, sql,connection, _dbConn.Transaction);
			foreach (object obj in parmList)
			{
				cmd.Parameters.Add((DbParameter)obj);
			}				
			_dbConn.Close();
			ActionCommand actionCommand = new ActionCommand (cmd, ObjectDataAccess.PredefinedActions.INSERT ,true);
			return actionCommand;
						
			return null;
		}


		/// <summary>
		/// Creates an update command
		/// </summary>
		/// <param name="schema">Schema of the object</param>
		/// <param name="connCache">The connection cache</param>
		/// <returns>Action command object that has been created</returns>
        public ActionCommand CreateUpdateCommand(ObjectSchema schema)
        {
            ISqlDataTypeTranslator translator = _configuration.Provider.GetDataTypeTranslator();
            UpdateQuery query = new UpdateQuery (schema.TableName);
            
            IDbConnection connection = _dbConn.Open();
            ArrayList parmList = new ArrayList();

            foreach (object key in schema.Mapping.Keys)
            {
                PropertyMap pmap = (PropertyMap)schema.Mapping[key];
                string propertyName = (string)pmap.ColumnName;
                if (!PropertyMap.ReferenceEquals(schema.Mapping.Primary, pmap))
                {
                    query.Items.Add ( new UpdateItem (propertyName, SqlExpression.Parameter 
                        (string.Format ("{0}{1}", translator.ParameterIdentifier,propertyName))));

                    DbParameter parm = CreateParameter();
                    parm.ParameterName =  string.Format ("{0}{1}", translator.ParameterIdentifier,propertyName);
                    parm.DbType = ObjectSchema.GetDBTypeFromSchemaRow(pmap.SchemaRow);
                    parm.Size = (int)pmap.SchemaRow["ColumnSize"];
                    parmList.Add(parm);
                }
            }
            
            query.WhereClause.Items.Add (WhereItem.CreateCompare 
                (SqlExpression.Field (schema.Mapping.Primary.ColumnName,FromItem.Table (schema.TableName)) ,
                SqlExpression.Parameter (string.Format ("{0}{1}", translator.ParameterIdentifier, schema.Mapping.Primary.ColumnName)), CompareOperator.Equal));

            DbParameter parmID = CreateParameter();
            parmID.ParameterName = string.Format ( "{0}{1}" , translator.ParameterIdentifier, schema.Mapping.Primary.ColumnName);
            parmID.DbType = ObjectSchema.GetDBTypeFromSchemaRow(schema.Mapping.Primary.SchemaRow);
            parmID.Direction = ParameterDirection.Input;
            parmList.Add(parmID);

            ISqlGenerator generator = _configuration.Provider.GetGenerator();
            string commandString = generator.GenerateUpdateStatement(query);

            IDbCommand cmd = DBObjectsFactory.CreateCommand(_configuration.Provider, commandString, connection, _dbConn.Transaction);

            foreach (object obj in parmList)
            {
                cmd.Parameters.Add((DbParameter)obj);
            }

            _dbConn.Close();
            ActionCommand actionCommand = new ActionCommand(cmd, ObjectDataAccess.PredefinedActions.UPDATE, true);
            return actionCommand;

        }

		/// <summary>
		/// Create a delete command
		/// </summary>
		/// <param name="schema">Schema of the object</param>
		/// <param name="connCache">The connection cache</param>
		/// <returns>Action command object that has been created</returns>
		public  ActionCommand CreateDeleteCommand(ObjectSchema schema )
		{
            ISqlDataTypeTranslator translator = _configuration.Provider.GetDataTypeTranslator();

            DeleteQuery query = new DeleteQuery(schema.TableName);
            query.WhereClause.Items.Add(WhereItem.CreateCompare(SqlExpression.Field(schema.Mapping.Primary.ColumnName),
                SqlExpression.Parameter(translator.ParameterIdentifier + schema.Mapping.Primary.ColumnName), CompareOperator.Equal));

			IDbConnection connection = _dbConn.Open();

			ArrayList parmList = new ArrayList();

            DbParameter parmID = CreateParameter();
            parmID.ParameterName = translator.ParameterIdentifier + schema.Mapping.Primary.ColumnName;
			parmID.DbType = ObjectSchema.GetDBTypeFromSchemaRow(schema.Mapping.Primary.SchemaRow); 
			parmID.Direction = ParameterDirection.Input;
			parmList.Add(parmID);

            ISqlGenerator generator = _configuration.Provider.GetGenerator();
            string commandString = generator.GenerateDeleteStatement(query);
			
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, commandString, connection, _dbConn.Transaction);
			
			foreach (object obj in parmList)
			{
				cmd.Parameters.Add((DbParameter)obj);
			}
			
			_dbConn.Close();
			ActionCommand actionCommand = new ActionCommand (cmd,ObjectDataAccess.PredefinedActions.DELETE,true);
			return actionCommand;
		}

		/// <summary>
		/// Create a delete all command
		/// </summary>
		/// <param name="schema">Schema of the object</param>
		/// <param name="connCache">The connection cache</param>
		/// <returns>Action command object that has been created</returns>
		public  ActionCommand CreateDeleteAllCommand(ObjectSchema schema )
		{
            DeleteQuery query = new DeleteQuery(schema.TableName);
		
	        // TODO : Check if truncate table is ANSI SQL 
            ISqlGenerator generator = _configuration.Provider.GetGenerator();
            query.WhereClause.Items.Add (WhereItem.CreateCompare(
                SqlExpression.Constant(SqlConstant.Number(1)), SqlExpression.Constant(SqlConstant.Number(1)),
                CompareOperator.Equal));
            string commandString = generator.GenerateDeleteStatement(query);

			IDbConnection  connection = _dbConn.Open();
			
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, commandString, connection, _dbConn.Transaction);
									
			_dbConn.Close();
			ActionCommand actionCommand = new ActionCommand (cmd,ObjectDataAccess.PredefinedActions.DELETE_ALL,true);
			return actionCommand;
		}


		public  ActionCommand CreateSelectCommand(ObjectSchema schema )
		{
            ISqlDataTypeTranslator translator = _configuration.Provider.GetDataTypeTranslator();
            
            SelectQuery query = new SelectQuery();

            foreach (PropertyMap map in schema.Mapping.Values)
            {
                query.Columns.Add(new SelectColumn(map.ColumnName, FromItem.Table(schema.TableName)));
            }
            query.FromClause.BaseTable = FromItem.Table(schema.TableName);

            query.WherePhrase.Items.Add(WhereItem.CreateCompare(
                SqlExpression.Field(schema.Mapping.Primary.ColumnName, FromItem.Table(schema.TableName)),
                SqlExpression.Parameter (string.Format("{0}{1}", translator.ParameterIdentifier, schema.Mapping.Primary.ColumnName)),
                CompareOperator.Equal));

			IDbConnection connection= _dbConn.Open();

			ArrayList parmList = new ArrayList();

            DbParameter parmID = CreateParameter();
            parmID.ParameterName = string.Format("{0}{1}", translator.ParameterIdentifier, schema.Mapping.Primary.ColumnName);
            parmID.DbType = ObjectSchema.GetDBTypeFromSchemaRow(schema.Mapping.Primary.SchemaRow); 
			parmID.Direction = ParameterDirection.Input;
			parmList.Add(parmID);

			string commandString = _configuration.Provider.GetGenerator().GenerateSelectStatement (query);
			
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, commandString, connection, _dbConn.Transaction);

			foreach (object obj in parmList)
			{
				cmd.Parameters.Add((DbParameter)obj);
			}
						
			
			_dbConn.Close();
			ActionCommand actionCommand = new ActionCommand (cmd,ObjectDataAccess.PredefinedActions.SELECT,true);
			return actionCommand;
		}

		public  ActionCommand CreateSelectAllCommand(ObjectSchema schema )
		{
            SelectQuery query = new SelectQuery();
            query.FromClause.BaseTable = FromItem.Table(schema.TableName);
            foreach (PropertyMap map in schema.Mapping.Values)
            {
                query.Columns.Add(new SelectColumn(map.ColumnName, FromItem.Table(schema.TableName)));
            }

            string commandString = _configuration.Provider.GetGenerator().GenerateSelectStatement(query);

			IDbConnection  connection = _dbConn.Open();
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, commandString, connection, _dbConn.Transaction);
			
			_dbConn.Close();

			ActionCommand actionCommand = new ActionCommand (cmd,ObjectDataAccess.PredefinedActions.SELECT_ALL,true);
			return actionCommand;
		}

		public  ActionCommand CreateSelectByCommand(ObjectSchema schema, WhereClause wherePhrase, 
            OrderByItemCollection orderByItems)
		{
            SelectQuery query = new SelectQuery();
            query.WherePhrase = (wherePhrase ==null ? new WhereClause() : wherePhrase);
            query.OrderByItems = (orderByItems  ==null ? new OrderByItemCollection() : orderByItems);
            
            query.FromClause.BaseTable = FromItem.Table(schema.TableName);
            foreach (PropertyMap map in schema.Mapping.Values)
            {
                query.Columns.Add(new SelectColumn(map.ColumnName, FromItem.Table(schema.TableName)));
            }
            string commandString = _configuration.Provider.GetGenerator().GenerateSelectStatement(query);
			IDbConnection  connection = _dbConn.Open ();
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, commandString, connection, _dbConn.Transaction);						
			_dbConn.Close();

			ActionCommand actionCommand = new ActionCommand (cmd,ObjectDataAccess.PredefinedActions.SELECT_BY,true);
			return actionCommand;
		}

        public ActionCommand CreateSelectByCommand(ObjectSchema schema, string wherePhrase,
                string orderByPhrase)
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            foreach (PropertyMap map in schema.Mapping.Values)
            {
                sb.AppendFormat("[{0}],", map.ColumnName);
            }
            sb.Remove(sb.Length-1, 1);
            sb.AppendFormat(" FROM [{0}]", schema.TableName);
            if (wherePhrase.Length > 0)
            {
                sb.AppendFormat(" WHERE {0}", wherePhrase);
            }
            if (orderByPhrase.Length > 0)
            {
                sb.AppendFormat(" ORDER BY {0}", orderByPhrase);
            }
            
            IDbConnection  connection = _dbConn.Open ();
            IDbCommand cmd = DBObjectsFactory.CreateCommand(_dbConn.Provider, sb.ToString(), connection, _dbConn.Transaction); 
            _dbConn.Close();
            ActionCommand actionCommand = new ActionCommand(cmd, ObjectDataAccess.PredefinedActions.SELECT_BY, false);
            return actionCommand;
        }


        public ActionCommand CreateDeleteByCommand(ObjectSchema schema, WhereClause wherePhrase)
        {
            DeleteQuery query = new DeleteQuery();
            query.WhereClause = (wherePhrase == null ? new WhereClause() : wherePhrase);
 
            query.TableName = schema.TableName;
            
            string commandString = _configuration.Provider.GetGenerator().GenerateDeleteStatement(query);
            IDbConnection connection = _dbConn.Open();
            IDbCommand cmd = DBObjectsFactory.CreateCommand(_dbConn.Provider, commandString, connection, _dbConn.Transaction);
            _dbConn.Close();

            ActionCommand actionCommand = new ActionCommand(cmd, ObjectDataAccess.PredefinedActions.DELETE_BY, true);
            return actionCommand;
        }

        public ActionCommand CreateSelectTopByCommand(ObjectSchema schema, int top, WhereClause wherePhrase,
            OrderByItemCollection orderByItems)
		{
            SelectQuery query = new SelectQuery();
            query.WherePhrase = (wherePhrase == null ? new WhereClause() : wherePhrase);
            query.OrderByItems = (orderByItems == null ? new OrderByItemCollection() : orderByItems);
            query.Top = top;

            query.FromClause.BaseTable = FromItem.Table(schema.TableName);
            foreach (PropertyMap map in schema.Mapping.Values)
            {
                query.Columns.Add(new SelectColumn(map.ColumnName, FromItem.Table(schema.TableName)));
            }
            string commandString = _configuration.Provider.GetGenerator().GenerateSelectStatement(query);
            IDbConnection connection = _dbConn.Open();
            IDbCommand cmd = DBObjectsFactory.CreateCommand(_dbConn.Provider, commandString, connection, _dbConn.Transaction);
            _dbConn.Close();

            ActionCommand actionCommand = new ActionCommand(cmd, ObjectDataAccess.PredefinedActions.SELECT_TOP_BY  , true);
            return actionCommand;
        }

        public ActionCommand CreateCustomSelectCommand(ObjectSchema schema, SelectQuery query)
        {
            query.FromClause.BaseTable = FromItem.Table(schema.TableName);
            string commandString = _configuration.Provider.GetGenerator().GenerateSelectStatement(query);
            
            IDbConnection connection = _dbConn.Open();
            IDbCommand cmd = DBObjectsFactory.CreateCommand(_dbConn.Provider, commandString, connection, _dbConn.Transaction);
            _dbConn.Close();

            ActionCommand actionCommand = new ActionCommand(cmd, ObjectDataAccess.PredefinedActions.SELECT, true);
            return actionCommand;
        }

        private DbParameter CreateParameter()
        {
            return _dbConn.Provider.CreateParameter();
        }
	}
}

